package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDALocation;
import ro.gateway.aida.obj.Country;
import ro.gateway.aida.obj.County;
import ro.gateway.aida.obj.Region;
import ro.gateway.aida.servlet.Constants;


/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: CountyDB.java,v 1.2 2005/01/26 10:35:58 mihaipostelnicu Exp $
 */
public class CountyDB extends DBPersistenceManager {
	private CountyDB(PersistenceToken token) {
		super(token);
	}

	public static CountyDB getManager(PersistenceToken token) {
		return new CountyDB(token);
	}

	public County[] getForRegion(int regiune_id)
		 throws SQLException {
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		County[] result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_FOR_REGION);
			ps.setInt(1, regiune_id);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				County item = new County();
				item.setId(rs.getInt(1));
				item.setName(rs.getString(2));
				item.setRegiune_id(regiune_id);
				items.add(item);
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new County[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public void insertCounty(County county)
		 throws SQLException {


		if (county.getId() == -1) {
			county.setId((int) db_token.getIdgenerators().
				 nextVal(Constants.IDGEN_COUNTIES));
		}

		Connection con = db_token.getCFService().getConnection(db_token.getPool_name());

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_COUNTY);
			ps.setInt(1, county.getId());
			ps.setInt(2, county.getRegiune_id());
			ps.setString(3, county.getName());
			ps.setDouble(4, county.getGeo_data().getLatitude());
			ps.setDouble(5, county.getGeo_data().getLongitude());
			ps.setString(6, county.getGeo_data().getGeo_code());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public void updateCounty(County county)
		 throws SQLException {


		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_COUNTY);
			ps.setString(1, county.getName());
			ps.setDouble(2, county.getGeo_data().getLatitude());
			ps.setDouble(3, county.getGeo_data().getLongitude());
			ps.setString(4, county.getGeo_data().getGeo_code());
			ps.setInt(5, county.getId());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public long getLastId() throws SQLException {
		Connection con = getConnection();
		long result = -1;

		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_ID);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = rs.getInt(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}

		return result;
	}

	public void deleteCounty(int id)
		 throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_COUNTY);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			throw e;
		} finally {
			con.close();
		}
	}

	public County getById(int id)
		 throws SQLException {
		Connection con = getConnection();
		County result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setInt(1, id);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = new County();
				result.setId(id);
				result.setName(rs.getString(1));
				result.setRegiune_id(rs.getInt(2));
				result.getGeo_data().setLatitude(rs.getDouble(3));
				result.getGeo_data().setLongitude(rs.getDouble(4));
				result.getGeo_data().setGeo_code(rs.getString(5));
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public AIDALocation[] findCounties(String what,
								int max_result_len)
		 throws SQLException {
		if (what == null)
			return null;
		Connection con = getConnection();
		AIDALocation[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(FIND_COUNTIES);
			ps.setString(1, what.toUpperCase() + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();

				Country country = new Country();
				Region region = new Region();
				County county = new County();
				county.setId(rs.getInt(1));
				county.setRegiune_id(rs.getInt(2));
				county.setName(rs.getString(3));
				region.setId(county.getRegiune_id());
				region.setCountryIso3(rs.getString(4));
				region.setName(rs.getString(5));
				country.setIso3(region.getCountryIso3());
				country.setName(rs.getString(6));

				item.setCountry(country);
				item.setJudet(county);
				item.setRegion(region);

				items.add(item);
				if (items.size() > max_result_len)
					break;
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public AIDALocation[] findCountiesIdentical(String what,
									    int max_result_len)
		 throws SQLException {
		if (what == null)
			return null;
		Connection con = getConnection();
		AIDALocation[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(FIND_COUNTIES);
			ps.setString(1, what.toUpperCase());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();

				Country country = new Country();
				Region region = new Region();
				County county = new County();
				county.setId(rs.getInt(1));
				county.setRegiune_id(rs.getInt(2));
				county.setName(rs.getString(3));
				region.setId(county.getRegiune_id());
				region.setCountryIso3(rs.getString(4));
				region.setName(rs.getString(5));
				country.setIso3(region.getCountryIso3());
				country.setName(rs.getString(6));

				item.setCountry(country);
				item.setJudet(county);
				item.setRegion(region);

				items.add(item);
				if (items.size() > max_result_len)
					break;
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}

		return result;
	}

	private static final String FIND_COUNTIES =
		 "SELECT cn.id, cn.regiune_id, cn.name, r.country_iso3, r.name, "
		 + "c.name "
		 + "FROM counties cn, countries_iso3 c, regions r "
		 + "WHERE (c.iso3=r.country_iso3) AND "
		 + "(r.id=cn.regiune_id) AND (cn.name ILIKE ?) ";
	public static final String DELETE_COUNTY =
		 "delete " + "from counties " + "where id=?";
	public static final String INSERT_COUNTY =
		 "insert into counties (id,regiune_id,name,gs_lat,gs_long,geo_code) " + "values (?,?,?,?,?,?)";
	public static final String UPDATE_COUNTY =
		 "UPDATE counties SET name=?,gs_lat=?,gs_long=?,geo_code=? WHERE id=?";
	public static final String GET_LAST_ID =
		 "select max(id) " + "from counties";
	public static final String GET_FOR_REGION =
		 "SELECT id, name " + "FROM counties " + "WHERE regiune_id=?";
	public static final String GET_BY_ID =
		 "SELECT name, regiune_id, gs_lat, gs_long, geo_code " +
		 "FROM counties WHERE id=?";
}
